# 简化的远程SQL Server连接测试脚本
Write-Host "=== 远程SQL Server连接测试 ===" -ForegroundColor Cyan

$server = "123.56.75.137"
$port = "1433"
$username = "sa"
$password = "q1w2e3r4..."
$database = "MES0825"

Write-Host "`n连接信息:" -ForegroundColor White
Write-Host "服务器: $server" -ForegroundColor Gray
Write-Host "端口: $port" -ForegroundColor Gray
Write-Host "用户名: $username" -ForegroundColor Gray
Write-Host "数据库: $database" -ForegroundColor Gray

# 1. 测试网络连接
Write-Host "`n1. 测试网络连接..." -ForegroundColor White
try {
    $pingResult = Test-NetConnection -ComputerName $server -Port $port -InformationLevel Quiet
    if ($pingResult) {
        Write-Host "✓ 网络连接成功" -ForegroundColor Green
    } else {
        Write-Host "✗ 网络连接失败" -ForegroundColor Red
    }
} catch {
    Write-Host "✗ 网络测试失败: $($_.Exception.Message)" -ForegroundColor Red
}

# 2. 检查sqlcmd工具
Write-Host "`n2. 检查SQL Server工具..." -ForegroundColor White
try {
    $sqlcmdPath = Get-Command sqlcmd -ErrorAction Stop
    Write-Host "✓ 找到sqlcmd工具: $($sqlcmdPath.Source)" -ForegroundColor Green
    
    # 测试连接
    Write-Host "`n3. 测试SQL Server连接..." -ForegroundColor White
    $testQuery = "SELECT @@VERSION as Version, GETDATE() as CurrentTime"
    $result = & sqlcmd -S "$server,$port" -U $username -P $password -Q $testQuery -h -1
    
    if ($LASTEXITCODE -eq 0) {
        Write-Host "✓ SQL Server连接成功" -ForegroundColor Green
        Write-Host "服务器信息:" -ForegroundColor Gray
        Write-Host $result -ForegroundColor Gray
        
        # 检查数据库
        Write-Host "`n4. 检查数据库状态..." -ForegroundColor White
        $dbQuery = "SELECT name, state_desc FROM sys.databases WHERE name = '$database'"
        $dbResult = & sqlcmd -S "$server,$port" -U $username -P $password -Q $dbQuery -h -1
        
        if ($dbResult -match $database) {
            Write-Host "✓ 数据库 '$database' 已存在" -ForegroundColor Green
        } else {
            Write-Host "⚠ 数据库 '$database' 不存在" -ForegroundColor Yellow
            Write-Host "尝试创建数据库..." -ForegroundColor Yellow
            
            $createDbQuery = "CREATE DATABASE [$database]"
            $createResult = & sqlcmd -S "$server,$port" -U $username -P $password -Q $createDbQuery -h -1
            
            if ($LASTEXITCODE -eq 0) {
                Write-Host "✓ 数据库 '$database' 创建成功" -ForegroundColor Green
            } else {
                Write-Host "✗ 数据库创建失败" -ForegroundColor Red
                Write-Host "错误信息:" -ForegroundColor Gray
                Write-Host $createResult -ForegroundColor Gray
            }
        }
    } else {
        Write-Host "✗ SQL Server连接失败" -ForegroundColor Red
    }
} catch {
    Write-Host "✗ 未找到sqlcmd工具" -ForegroundColor Red
    Write-Host "建议安装SQL Server客户端工具" -ForegroundColor Yellow
}

Write-Host "`n=== 解决方案建议 ===" -ForegroundColor Cyan
Write-Host "如果数据库创建失败，可能的原因:" -ForegroundColor White
Write-Host "1. 服务器磁盘空间不足" -ForegroundColor Gray
Write-Host "2. SA账户权限不足" -ForegroundColor Gray
Write-Host "3. 服务器资源限制" -ForegroundColor Gray
Write-Host "4. 联系服务器管理员检查服务器状态" -ForegroundColor Gray

Write-Host "`n✓ 测试完成!" -ForegroundColor Green
